import logging

import psycopg2
from database.database_conf import DatabaseConf
from database.database_object import DatabaseObject


class PostgresObject(DatabaseObject):

    def connect(self, db_conf: DatabaseConf) -> bool:
        self.db_conf = db_conf
        ret = True
        try:
            dsn = f"{db_conf.db_address}:{db_conf.db_port}/{db_conf.db_name}"
            self.db_url = f"Postgres[{dsn}][{db_conf.db_username}][{db_conf.db_password}]"
            logging.info(self.db_url)
            self.db_con = psycopg2.connect(host=db_conf.db_address,
                                           port=db_conf.db_port,
                                           user=db_conf.db_username,
                                           password=db_conf.db_password,
                                           dbname=db_conf.db_name)
        except Exception as ex:
            ret = False
            self.last_error = str(ex)
            logging.error(ex)
        return ret

    def get_version(self) -> str:
        sql = "SELECT version() AS DB_VERSION"
        row = self.fetchone(sql)
        if row:
            return row[0]
        return ""

    def get_schema_list(self) -> list:
        schema_list = []
        sql = "SELECT SCHEMA_NAME AS DB_NAME FROM INFORMATION_SCHEMA.SCHEMATA"
        rows = self.fetchall(sql)
        for row in rows:
            schema_list.append(row[0])
        return schema_list

    def get_table_list(self, schema_name: str) -> list:
        table_list = []
        sql = "select TABLE_NAME from information_schema.tables where table_catalog=%s and table_schema=%s and table_type='BASE TABLE' ORDER BY table_name"
        rows = self.fetchall(sql, params=[self.db_conf.db_name, schema_name])
        for row in rows:
            table_list.append(row[0])
        return table_list

    def get_filed_list(self, schema_name: str, table_name: str) -> list:
        filed_list = []
        sql = "select column_name  as field_name ,udt_name as field_type,character_maximum_length as field_length,'' as field_desc from information_schema.columns  where table_catalog=%s and table_schema=%s and table_name=%s"
        rows = self.fetchall_dict(sql, params=[self.db_conf.db_name, schema_name, table_name])
        for row in rows:
            filed_row = dict()
            filed_row["field_name"] = row["field_name"]
            field_type = row["field_type"]
            if row["field_length"]:
                field_type = f"{row["field_type"]}({row["field_length"]})"
            filed_row["field_type"] = field_type
            filed_row["field_length"] = row["field_length"]
            filed_row["field_desc"] = row["field_desc"]

            filed_list.append(filed_row)
        return filed_list
